import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class FindPath {
	public static final String LINE_STATION_TABLE = "LINE_STATION";
	
	private static FindPath instance = null;
	public static FindPath getInstance() throws SQLException {
		if (instance == null) {
			instance = new FindPath();
			return instance;
		} else {
			return instance;
		}
	}
	protected FindPath() throws SQLException {
		conn = Database.getAccessDBConnection();
	}
	Connection conn = null;
	
	private Path getPath(BusLine line, Station start, Station end) {
		Path path = new Path();
		path.noOfBusLine = 1;
		path.line = line;
		try {
			StationManager sm = StationManager.getInstance();
			ArrayList<Station> stations = sm.getStationInLine(line.getId());
			int startIndex = 0, endIndex = 0;
			
			int size = stations.size(); 
			for (int i = 0; i < size; i++) {
				Station station = stations.get(i);
				if (station.getId() == start.getId()) {
					startIndex = i;
				} else if (station.getId() == end.getId()) {
					endIndex = i;
				}
			}
			
			if (startIndex < endIndex) {
				for (int i = startIndex; i <= endIndex; i++) {
					path.stations.add(stations.get(i));
				}
			} else if (startIndex > endIndex){
				for (int i = startIndex; i >= endIndex; i--) {
					path.stations.add(stations.get(i));
				}
			}
		} catch (SQLException e) {
		}
		
		return path;
	}
	
	private Path getPath(BusLine line, BusLine line2, Station start, Station end) {
		Path path = new Path();
		path.noOfBusLine = 2;
		path.line = line;
		path.line2 = line2;
		
		try {
			StationManager sm = StationManager.getInstance();
			ArrayList<Station> stations = sm.getStationInLine(line.getId());
			ArrayList<Station> stations2 = sm.getStationInLine(line2.getId());
			
			int startIndex = 0, endIndex = 0, minIndex1 = 0, minIndex2 = 0;
			int minLength = Integer.MAX_VALUE;
			
			int size = stations.size(); 
			for (int i = 0; i < size; i++) {
				Station station = stations.get(i);
				if (station.getId() == start.getId()) {
					startIndex = i;
				}
			}
			
			int size2 = stations2.size(); 
			for (int i = 0; i < size2; i++) {
				Station station = stations2.get(i);
				if (station.getId() == end.getId()) {
					endIndex = i;
				}
			}
			
			for (int i = 0; i < size; i++) {
				for (int j = 0; j < size2; j++) {
					Station station = stations.get(i);
					Station station2 = stations2.get(j);
					if (station.getId() == station2.getId()) {
						if (Math.abs(i - startIndex) + Math.abs(j - endIndex) < minLength) {
							minIndex1 = i;
							minIndex2 = j;
							minLength = Math.abs(i - startIndex) + Math.abs(j - endIndex);
						}
					}
				}
			}
			
			if (startIndex < minIndex1) {
				for (int i = startIndex; i <= minIndex1; i++) {
					path.stations.add(stations.get(i));
				}
			} else if (startIndex > minIndex1){
				for (int i = startIndex; i >= minIndex1; i--) {
					path.stations.add(stations.get(i));
				}
			}
			
			if (endIndex < minIndex2) {
				for (int i = minIndex2; i >= endIndex; i--) {
					path.stations2.add(stations2.get(i));
				}
			} else if (endIndex > minIndex2){
				for (int i = minIndex2; i <= endIndex; i++) {
					path.stations2.add(stations2.get(i));
				}
			}
		} catch (SQLException e) {
		}
		
		return path;
	}
	
	public ArrayList<Path> find(Station start, Station end) throws SQLException {
		ArrayList<Path> paths = new ArrayList<Path>();
		CallableStatement cs = conn.prepareCall("EXEC sp_FindPath " + start.getId() + "," + end.getId());
		ResultSet rs = cs.executeQuery();
		BusLineManager blm = BusLineManager.getInstance();
		while (rs.next()) {
			try {
				if (rs.getMetaData().getColumnCount() > 1) {
					int line1 = rs.getInt("line1");
					int line2 = rs.getInt("line2");
					BusLine bl1 = blm.getBusLine(line1);
					BusLine bl2 = blm.getBusLine(line2);
					paths.add(getPath(bl1, bl2, start, end));
				} else {
					int line = rs.getInt("line_id");
					BusLine bl = blm.getBusLine(line);
					paths.add(getPath(bl, start, end));
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return paths;
	}
	
	public ArrayList<Path> find2(Station start, Station end) {
		ArrayList<Path> paths = new ArrayList<Path>();
		try {
			BusLineManager bm = BusLineManager.getInstance();
			ArrayList<BusLine> lines1 = bm.getBusLineOverStation(start.getId());
			ArrayList<BusLine> lines2 = bm.getBusLineOverStation(end.getId());
			ArrayList<BusLine> tmp = new ArrayList<BusLine>();
			
			for (BusLine line : lines1) {
				for (BusLine line2 : lines2) {
					if (line.getId() == line2.getId()) {
						if (!tmp.contains(line)) {
							tmp.add(line);
						}
					}
				}
			}
			
			for (BusLine line : tmp) {
				paths.add(getPath(line, start, end));
			}
			if (paths.size() > 0) return paths;
			
			for (BusLine line : lines1) {
				for (BusLine line2 : lines2) {
					if (line.getId() != line2.getId()) {
						try {
							Statement stmt = conn.createStatement();
							String sql = "SELECT * FROM " + LINE_STATION_TABLE + " t1, " + LINE_STATION_TABLE + " t2 "
									+ " WHERE t1.line_id=" + line.getId() + " AND t2.line_id=" + line2.getId()
									+ " AND t1.station_id=t2.station_id";
							stmt.execute(sql);
							ResultSet rs = stmt.getResultSet();
							if (rs.next()) {
								paths.add(getPath(line, line2, start, end));
							}
						} catch (SQLException e) {
						}
					}
				}
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return paths;
	}
}

class Path {
	int noOfBusLine = 0;
	BusLine line = null;
	ArrayList<Station> stations = new ArrayList<Station>();
	BusLine line2 = null;
	ArrayList<Station> stations2 = new ArrayList<Station>();
}
